﻿using Dapper;
using Domain.Entity.Basic_information;
using Domain.Entity.System_operation;
using Domain.IRepository.System_operation;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.System_operation
{
    [Dependency(ServiceLifetime.Scoped)]
    public class Arrival_meterRepository: IArrival_meterRepository
    {
        public MyDbContext myDbContext { get; set; }

        public Arrival_meterRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        public int Count(string? RealityGet_time, string? value2,string? Origin_place,string? Det_place)
        {
            string sql = "select [Bill_Id],[Wb_number],[Order_id],[Customer],[Origin_place],[Det_place],[Is_arrival],[Pickup_address],[Delivery_address]," +
                "[Driver_name],[Car_number],[Bill_nums],[Bill_volume],[Bill_weight],[Bill_cost]," +
                "[Driver_phone],[Pickup_time],[Pickup_endtime],[Plan_time],[Reality_time],[PlanGet_time],[RealityGet_time] " +
                "from [dbo].[Arrival_meter] inner join [dbo].[Bill_ol] on [dbo].[Arrival_meter].Billol_Id=[dbo].[Bill_ol].Billol_Id";
            if (RealityGet_time != null && RealityGet_time != "")
            {
                string[] time1 = RealityGet_time.Split(',');
                sql += $" and [RealityGet_time]>='{time1[0]}' and [RealityGet_time]<='{time1[1]}'";
            }
            if (value2 != null && value2 != "")
            {
                sql += $"AND (Customer LIKE '%{value2}%')";
            }
            if (Origin_place != null && Origin_place != "")
            {
                sql += $" and [Origin_place] like '%{Origin_place}%'";
            }
            if (Det_place != null && Det_place != "")
            {
                sql += $" and [Det_place] like '%{Det_place}%'";
            }
            int count = myDbContext.Database.GetDbConnection().Query<Arrival_meter>(sql).Count();
            return count;
        }

        public List<Arrival_meter> GetArrival_meters()
        {
            /*string sql= "select [Bill_Id],[Wb_number],[Order_Id],[Customer],[Origin_place],[Det_place],[Is_arrival],[Pickup_address],[Delivery_address]," +
                "[Driver_name],[Car_number],[Bill_nums],[Bill_volume],[Bill_weight],[Bill_cost]," +
                "[Driver_phone],[Pickup_time],[Pickup_endtime],[Plan_time],[Reality_time],[PlanGet_time],[Reality_time] " +
                "from [dbo].[Arrival_meter] inner join [dbo].[Bill_ol] on [dbo].[Arrival_meter].Billol_Id=[dbo].[Bill_ol].Billol_Id";
            List<Arrival_meter> list = myDbContext.Database.GetDbConnection().Query<Arrival_meter>(sql).ToList();
            var ArList = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return ArList;*/
            string sql = "SELECT * FROM [dbo].[Arrival_meter]";
            return myDbContext.Database.GetDbConnection().Query<Arrival_meter>(sql).ToList();
        }

        public Bill_ol GetBillId(int id)
        {
            string sql = $"SELECT * FROM [dbo].[Bill_ol] where [Billol_Id]={id}";
            return myDbContext.Database.GetDbConnection().Query<Bill_ol>(sql).FirstOrDefault();
        }
        public bool DDArrival(int id)
        {
            string sql = $"update Arrival_meter set Is_arrival=0 where [Arrival_Id]={id}";
            return myDbContext.Database.GetDbConnection().Execute(sql) > 0;
        }

        public bool ZCArrival(string Wb_number)
        {
            bool flag = false;
            string sql = $"select * FROM [dbo].[Bill_ol] WHERE [Wb_number]='{Wb_number}'";
            List<Bill_ol> list = myDbContext.Database.GetDbConnection().Query<Bill_ol>(sql).ToList();
            foreach(Bill_ol bill in list)
            {
                sql = $"UPDATE [dbo].[Arrival_meter] set [Is_arrival]=0 WHERE [Billol_Id]={bill.Billol_Id}";
                int result = myDbContext.Database.ExecuteSqlRaw(sql);
                if(result > 0)
                {
                    flag = true;
                }
                else
                {
                    flag = false;
                    return flag;
                }
            }
            return flag;
        }

        public List<Arrival_meter> Arrival_condition(string? RealityGet_time, string? value2, string Origin_place, string? Det_place, int currentPage, int PageSize)
        {
            string sql = "SELECT [Arrival_Id], A.[Billol_Id], [Is_arrival] FROM [dbo].[Arrival_meter] A,[dbo].[Bill_ol] B where 1=1 ";
            /*string sql = $"select [Bill_Id],[Wb_number],[Order_Id],[Customer],[Origin_place],[Det_place],Is_arrival=(select [Is_arrival] from [dbo].[Arrival_meter] where [Billol_Id]=[Billol_Id]),[Pickup_address],[Delivery_address],[Driver_name],[Car_number],[Bill_nums],[Bill_volume],[Bill_weight],[Bill_cost],[Driver_phone],[Pickup_time],[Pickup_endtime],[Plan_time],[Reality_time],[PlanGet_time],[Reality_time] from Bill_ol WHERE 1=1 ";*/
            if (RealityGet_time != null && RealityGet_time != "")
            {
                string[] time1 = RealityGet_time.Split(',');
                sql += $" and [RealityGet_time]>='{time1[0]}' and [RealityGet_time]<='{time1[1]}'";
            }
            if (value2 != null && value2 != "")
            {
                sql += $" AND (Customer LIKE '%{value2}%')";
            }
            if (Origin_place != null && Origin_place != "")
            {
                sql += $" and [Origin_place] like '%{Origin_place}%'";
            }
            if (Det_place != null && Det_place != "")
            {
                sql += $" and [Det_place] like '%{Det_place}%'";
            }
            List<Arrival_meter> list = myDbContext.Database.GetDbConnection().Query<Arrival_meter>(sql).ToList();
            var arrival = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return arrival;
        }
    }
}
